from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import xlrd
# from emmodels.em_realtradehis import *
from emmodels.em_base_trade_his2 import BaseTradeHis

r = range(6)
insertCount = 0
zx_list = []
for m in r[1:]:
    path = '/Users/stevenbob/Documents/TradeRecord/ZX/'+str(m)+'.xls'
    data = xlrd.open_workbook(path)
    print(path)
    table = data.sheets()[0]
    nrows = table.nrows
    for i in range(nrows):
        if i == 0:continue
        # zxHis = ZXTradeHis(table.row_values(i)[:])
        zxRecord = table.row_values(i)[:]
        basehis = BaseTradeHis(zxRecord[0], zxRecord[1], zxRecord[3], zxRecord[4], zxRecord[12], zxRecord[6], zxRecord[7],
                             zxRecord[8], zxRecord[12],'ZX')
        if basehis.validate():
            insertCount += 1
            zx_list.append(basehis)
            print(table.row_values(i)[:], "\n")
BaseTradeHis.insert_list(zx_list)

print("一共插入ZX"+str(insertCount)+"条数据")

r = range(3)
yh_list = []
for m in r[1:]:
    path = '/Users/stevenbob/Documents/TradeRecord/YH/'+str(m)+'.xls'
    data = xlrd.open_workbook(path)
    print(path)
    table = data.sheets()[0]
    nrows = table.nrows
    for i in range(nrows):
        if i == 0:continue
        # yhHis = YHTradeHis(table.row_values(i)[:])
        yhRecord = table.row_values(i)[:]
        base_yinhe = BaseTradeHis(yhRecord[0], yhRecord[1], yhRecord[2], yhRecord[3], yhRecord[4], yhRecord[5], yhRecord[6],
                             yhRecord[7],yhRecord[4],'YH')
        if base_yinhe.validate():
            insertCount += 1
            yh_list.append(yhRecord)
            print(table.row_values(i)[:], "\n")
BaseTradeHis.insert_list(yh_list)
print("一共插入YH"+str(insertCount)+"条数据")


